package yu.zhou.njupt.importdata.core.impl;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.dbutils.DbUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;

import yu.zhou.njupt.importdata.core.Importer;
import yu.zhou.njupt.importdata.entity.ImportResult;
import yu.zhou.njupt.importdata.entity.enumtype.CreateTable;
import yu.zhou.njupt.importdata.entity.enumtype.DoTruncate;
import yu.zhou.njupt.importdata.entity.enumtype.NewImport;
import yu.zhou.njupt.importdata.util.RegexUtil;

import com.google.common.base.Joiner;

/** 
 * 用于导入xlsx文件，
 * 一个文件对应一张表，文件名为表名，
 * 文件里可以有多个sheet，sheet的
 * 结构完全一致，全是表里的数据 
**/
public class XlsImporter extends Importer{
    
    public XlsImporter(Connection conn, DoTruncate doTruncate, NewImport newImport, CreateTable createTable, int titleNum) throws Exception {
        super(conn, doTruncate, newImport, createTable, titleNum);
    }

    public ImportResult importData(File sourceFile,
            List<String> disableConsStrSet, List<String> enableConsStrSet, 
            List<String> disableIndexesStrSet, List<String> enableIndexesStrSet){
        int count = 0;
        int recordCount = 0;
        long elapsedTime = 0;
        ImportResult importResult = null;
        PreparedStatement ps = null;
        FileInputStream fis = null;
        String filename = null;
        String sheetName = null;

        try {
            Date startTime = new Date();
            filename = sourceFile.getName();
            String tableName = null;
            boolean isSingleTable = filename.startsWith("$-");
            String partKey = RegexUtil.getDateStr(filename);
            if (isSingleTable) {
                tableName = RegexUtil.getValidName(filename).toUpperCase();
            }
            fis = new FileInputStream(sourceFile);
            HSSFWorkbook workbook = new HSSFWorkbook(fis);
            HSSFSheet sheet = null;
            
            for (int sheetIndex = 0; ;sheetIndex++) {
                try {
                    sheet = workbook.getSheetAt(sheetIndex);
                    sheetName = sheet.getSheetName();
                } catch (Exception e) {
                    break;
                }
                if (!hasDealt(filename, sheetName)) {
                    if (!isSingleTable) {
                        tableName = RegexUtil.getValidName(sheetName).toUpperCase();
                    }
                    if (partKey != null) {
                        try {
                            Statement s = conn.createStatement();
                            /* 添加分区 (如果分区已存在，会报出异常，但是只要分区存在，后面的都可正常执行，这里直接将异常catch住)*/
                            s.execute("alter table " + tableName
                                    + " add partition p" + partKey + " values('"
                                    + partKey + "')");
                        } catch (Exception e) {
                        }
                    }
                    System.out.println("    Sheet:" + sheetName + "(表：" + tableName + ")...");
                    doBeforeImport(tableName, disableConsStrSet, enableConsStrSet, disableIndexesStrSet, enableIndexesStrSet);
                    HSSFRow titleRow = sheet.getRow(0);
                    int columnNum = titleRow.getLastCellNum() - titleRow.getFirstCellNum(); // getFirstCellNum返回0,getLastCellNum()返回列数
                    List<String> columnList = new ArrayList<String>();
                    List<String> placeholderList = new ArrayList<String>();
                    List<Integer> indexList = new ArrayList<Integer>();
                    for (int i = titleRow.getFirstCellNum(); i < columnNum; i++) {
                        Cell columnCell = titleRow.getCell(i);
                        columnCell.setCellType(Cell.CELL_TYPE_STRING);
                        String cellName = columnCell.getStringCellValue();
                        if (!cellName.startsWith("@")) {
                            String columnName = RegexUtil.getValidName(cellName);
                            if (columnName != null) {
                                columnList.add(columnName);
                                placeholderList.add("?");
                                indexList.add(i);
                            }
                        }
                    }
                    String insertStr = "insert into " + tableName + " (" + Joiner.on(',').join(columnList) + ") " + "values (" + Joiner.on(',').join(placeholderList) + ")";
                    System.out.println("  导入语句：" + insertStr);
                    ps = conn.prepareStatement(insertStr);
                    /* getFirstRowNum()返回0,getLastRowNum()返回去掉标题行后的行数 */
                    for (int rownum = titleNum; rownum <= sheet.getLastRowNum(); rownum++) { 
                        if (null != sheet.getRow(rownum)) {
                            HSSFRow row = sheet.getRow(rownum); // 获得一个行
                            int paramIndex = 1;
                            for(Integer i : indexList){
                                Cell cell = row.getCell(i);
                                if (cell == null) {
                                    ps.setString(paramIndex++, null);
                                } else {
                                    cell.setCellType(Cell.CELL_TYPE_STRING);
                                    ps.setString(paramIndex++, cell.getStringCellValue().trim());
                                }
                            }
                            ps.addBatch();
                            count ++;
                            if (count == 10000) {
                                ps.executeBatch();
                                ps.clearBatch();
                                count = 0;
                                recordCount += 10000;
                                if (recordCount % 1000000 == 0) {
                                    System.out.println("已插入" + recordCount + "条记录.");                        
                                }
                            }
                        }
                    }
                    ps.executeBatch();
                    ps.clearBatch();
                    recordCount += count;
                    doAfterImport(tableName, filename, sheetName);
                    System.out.println("  导入完成.记录数：" + recordCount);
                    System.out.println(String.format("%-100s", "-").replace(' ', '-'));
                    recordCount = 0;
                    count = 0;
                }
            }
            
            Date endTime = new Date();
            elapsedTime = endTime.getTime() - startTime.getTime();
            importResult = new ImportResult(true, recordCount, elapsedTime);
        } catch (Exception e) {
            e.printStackTrace();
            System.out.println("  导入失败");
            importResult = new ImportResult(false);
        } finally {
            DbUtils.closeQuietly(ps);
            try {
                fis.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        
        return importResult;
    }
    
}
